﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SqlClient;

namespace ClassGenerator.Lib
{
    public class Table
    {
        public string TABLE_NAME { get; set; }
        public string COLUMN_NAME { get; set; }
        public string IS_NULLABLE { get; set; }
        public string DATA_TYPE { get; set; }
        public bool PRIMARY_KEY { get; set; }

        public DBConnectionParameters DATABASE_CONNECTION_PARAMETERS { get; set; }

        public List<Table> getAllTablesAndColumns()
        {
            List<Table> tables = new List<Table>();
            
            using (IDbConnection connection = new SqlConnection(DATABASE_CONNECTION_PARAMETERS.getConnectionString()))
            {
                connection.Open();

                string query =
                @"SELECT ISC.TABLE_NAME, ISC.COLUMN_NAME, ISC.IS_NULLABLE, ISC.DATA_TYPE, CAST((CASE WHEN ISCCU.COLUMN_NAME IS NULL THEN 0 ELSE 1 END) AS BIT) AS PRIMARY_KEY
                FROM INFORMATION_SCHEMA.COLUMNS AS ISC
                LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ISCCU ON ISC.TABLE_NAME = ISCCU.TABLE_NAME AND ISC.COLUMN_NAME = ISCCU.COLUMN_NAME
                ORDER BY ISC.TABLE_NAME";

                tables = connection.Query<Table>(query, new DynamicParameters()).ToList();
            }

            return tables;
        }
    }
}
